﻿Imports System.Data
Imports PadovadoAPI
Imports System.Web.Routing

Partial Class TabWeek
    Inherits System.Web.UI.UserControl


    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        CaricaRepeater()
        ' BtnSearch.Focus()

    End Sub


    Public Sub CaricaRepeater()
        'Dim diz As New Dizionario
        Dim sql As String = ""
        ' Try

        Dim ds As New DataSet
        Dim FK_target As New Integer
        Dim dbConnector As New DBObject
        Dim CurrentDate As DateTime = DateTime.Now.Date()


        'Dim rptEventis As New List(Of Repeater)
        'rptEventis.Add(RptEventi1)
        'rptEventis.Add(RptEventi2)

        '  For i As Integer = 0 To 1
        sql = "SELECT TOP (6) max(T.PK_Target) as FK_Target, I.Comune as Comune, E.NomeEvento as NomeEvento, E.PK_Evento," & _
            "E.DataInizio, E.DataFine, E.Priorita, E.Descrizione as DescrizioneEvento, " & _
          " CASE isnull(E.imgEvento, '') WHEN '' then MAX(T.TargetImgURLSmall) else E.imgEvento end as ImgEvento, " & _
          " convert(varchar,E.DataInizio,103) as DataInizio, " & _
          " convert(varchar,E.DataFine,103) as DataFine, E.Link, E.Brochure, " & _
          " CASE isnull(E.intCategoria, 100) " & _
                      "     WHEN 100 then 'Generico' " & _
                      "     WHEN 1 then 'Concerto'" & _
                      "     WHEN 2 then 'Festa'" & _
                      "     WHEN 3 then 'Discoteca'" & _
                      "     WHEN 4 then 'Fiera'" & _
                      "     WHEN 5 then 'Sagra'" & _
                      "     WHEN 6 then 'Gita'" & _
                      "     WHEN 7 then 'Gratis'" & _
                      "     WHEN 8 then 'Mostra' end as Genere, " & _
          " CASE isnull(E.link, '') WHEN '' then 0 else 1 end as LinkPresente, " & _
          " CASE isnull(E.Brochure, '') WHEN '' then 0 else 1 end as BrochurePresente, " & _
               " isnull(I.Strada,'') + ' ' + isnull(I.Civico,'') + ' ' + isnull(I.Comune,'') + ' ' + isnull(I.Presso,'') as Indirizzo, " & _
                 " MAX(T.TargetImgURLSmall) as TargetImgURLSmall, " & _
                 " A.Denominazione as Organizzatore, " & _
                 " ROW_NUMBER() OVER(ORDER BY E.DataInizio) AS 'RowNumber'" & _
                 " FROM Evento E " & _
                 " LEFT OUTER JOIN Indirizzo I ON I.PK_Indirizzo = FK_Indirizzo " & _
                 " LEFT OUTER JOIN Evento_Organizzatore EO ON PK_Evento = EO.FK_Evento " & _
                 " LEFT OUTER JOIN Organizzatore O ON O.PK_Organizzatore = EO.FK_Organizzatore " & _
                 " LEFT OUTER JOIN Anagrafica A ON A.PK_Anagrafica = O.FK_Anagrafica " & _
                 " LEFT OUTER JOIN Evento_Target ET ON ET.FK_Evento = PK_Evento " & _
                 " LEFT OUTER JOIN Target T ON T.PK_Target = ET.FK_Target " & _
                 " WHERE E.Deleted = 0 AND E.DataInizio >= CAST('" & CurrentDate.ToString("yyyyMMdd") & "' AS DATE) " & _
                 " GROUP BY E.NomeEvento, E.Descrizione, E.DataInizio, E.DataFine, E.Link, I.Strada, I.Civico, I.Comune, I.Presso, PK_Evento, E.imgEvento, E.Brochure, E.Priorita," & _
                 " E.intCategoria, A.Denominazione" & _
                 " ORDER BY PK_Evento DESC, Comune "
        '  Throw New Exception(sql)


        ds = dbConnector.FillDS(sql, Nothing, 1)

        If Not IsNothing(ds) AndAlso ds.Tables.Count > 0 Then
            ds.Tables(0).Columns.Add("ItemTemplateDefId")
            ds.Tables(0).Columns.Add("ImageApriDefId")
            ds.Tables(0).Columns.Add("LinkInterno")
            For Each dr As DataRow In ds.Tables(0).Rows

                dr("ItemTemplateDefId") = "ItemTemplateDef_" & dr("RowNumber")
                dr("ImageApriDefId") = "ImageApriDef_" & dr("RowNumber")
                Dim strDay = ""
                If Not IsDBNull(dr("DataInizio")) AndAlso Not IsDBNull(dr("DataFine")) AndAlso dr("DataInizio") = dr("DataInizio") Then
                    Dim wd As Integer = Weekday(CDate(dr("DataInizio")))

                    Select Case wd
                        Case 1
                            strDay = "Domenica"
                        Case 2
                            strDay = "Lunedì"
                        Case 3
                            strDay = "Martedì"
                        Case 4
                            strDay = "Mercoledì"
                        Case 5
                            strDay = "Giovedì"
                        Case 6
                            strDay = "Venerdì"
                        Case 7
                            strDay = "Sabato"
                    End Select
                End If

                Dim parameters As RouteValueDictionary
                parameters = New RouteValueDictionary(New With {.TipoLocale = Trim(dr("Genere")), .NomeLocale = Trim(dr("Organizzatore")), .TitoloEvento = Trim(dr("NomeEvento"))})

                Dim vpd As VirtualPathData
                vpd = RouteTable.Routes.GetVirtualPath(Nothing, "PaginaEvento", parameters)

                dr("NomeEvento") = dr("Comune") & " - " & strDay & " " & dr("DataInizio") & " - " & dr("Organizzatore") & " - " & dr("NomeEvento")
                'dr("LinkInterno") = dr("LinkInterno") & "&NomeEvento=" & dr("NomeEvento")

                dr("LinkInterno") = vpd.VirtualPath & "?PK_Evento=" & dr("PK_Evento")
            Next
            ' Throw New Exception(ds.Tables.Count)
            RptEventi1.DataSource = ds
            RptEventi1.DataBind()
        End If

        sql = "SELECT max(T.PK_Target), I.Comune as Comune, E.NomeEvento as NomeEvento,  E.PK_Evento," & _
            "E.DataInizio, E.DataFine, E.Priorita, E.Descrizione as DescrizioneEvento, " & _
          " CASE isnull(E.imgEvento, '') WHEN '' then MAX(T.TargetImgURLSmall) else E.imgEvento end as ImgEvento, " & _
          " convert(varchar,E.DataInizio,103) as DataInizio, " & _
          " convert(varchar,E.DataFine,103) as DataFine, E.Link, E.Brochure, " & _
           " CASE isnull(E.intCategoria, 100) " & _
                      "     WHEN 100 then 'Generico' " & _
                      "     WHEN 1 then 'Concerto'" & _
                      "     WHEN 2 then 'Festa'" & _
                      "     WHEN 3 then 'Discoteca'" & _
                      "     WHEN 4 then 'Fiera'" & _
                      "     WHEN 5 then 'Sagra'" & _
                      "     WHEN 6 then 'Gita'" & _
                      "     WHEN 7 then 'Gratis'" & _
                      "     WHEN 8 then 'Mostra' end as Genere, " & _
          " CASE isnull(E.link, '') WHEN '' then 0 else 1 end as LinkPresente, " & _
          " CASE isnull(E.Brochure, '') WHEN '' then 0 else 1 end as BrochurePresente, " & _
               " isnull(I.Strada,'') + ' ' + isnull(I.Civico,'') + ' ' + isnull(I.Comune,'') + ' ' + isnull(I.Presso,'') as Indirizzo, " & _
                 " MAX(T.TargetImgURLSmall) as TargetImgURLSmall, " & _
                 " A.Denominazione as Organizzatore, " & _
                 " ROW_NUMBER() OVER(ORDER BY E.DataInizio) AS 'RowNumber'" & _
                 " FROM Evento E " & _
                 " LEFT OUTER JOIN Indirizzo I ON I.PK_Indirizzo = FK_Indirizzo " & _
                 " LEFT OUTER JOIN Evento_Organizzatore EO ON PK_Evento = EO.FK_Evento " & _
                 " LEFT OUTER JOIN Organizzatore O ON O.PK_Organizzatore = EO.FK_Organizzatore " & _
                 " LEFT OUTER JOIN Anagrafica A ON A.PK_Anagrafica = O.FK_Anagrafica " & _
                 " LEFT OUTER JOIN Evento_Target ET ON ET.FK_Evento = PK_Evento " & _
                 " LEFT OUTER JOIN Target T ON T.PK_Target = ET.FK_Target " & _
                 " WHERE E.Deleted= 0 AND (E.DataInizio = CAST('" & CurrentDate.ToString("yyyyMMdd") & "' AS DATE)" & _
                    " OR (E.DataInizio < CAST('" & CurrentDate.ToString("yyyyMMdd") & "' as date) " & _
                    " AND E.DataFine >= CAST('" & CurrentDate.ToString("yyyyMMdd") & "' as date)))" & _
                 " GROUP BY E.NomeEvento, E.Descrizione, E.DataInizio, E.DataFine, E.Link, I.Strada, I.Civico, I.Comune, I.Presso, PK_Evento, E.imgEvento, E.Brochure, E.Priorita, " & _
                 " E.intCategoria, A.Denominazione" & _
                  " ORDER BY E.Priorita DESC, Comune "

        ' Throw New Exception(sql)
        ds = dbConnector.FillDS(sql, Nothing, 1)

        '<%#DataBinder.Eval(Container.DataItem, "ImageTargetId")%>'
        If Not IsNothing(ds) AndAlso ds.Tables.Count > 0 Then
            ds.Tables(0).Columns.Add("ItemTemplateId")
            ds.Tables(0).Columns.Add("ImageApriId")
            ds.Tables(0).Columns.Add("LinkInterno")
            For Each dr As DataRow In ds.Tables(0).Rows
                dr("ItemTemplateId") = "ItemTemplate_" & 1 & "_" & dr("RowNumber")
                dr("ImageApriId") = "ImageApri_" & 1 & "_" & dr("RowNumber")
                Dim strDay = ""
                If Not IsDBNull(dr("DataInizio")) AndAlso Not IsDBNull(dr("DataFine")) AndAlso dr("DataInizio") = dr("DataInizio") Then
                    Dim wd As Integer = Weekday(CDate(dr("DataInizio")))

                    Select Case wd
                        Case 1
                            strDay = "Domenica"
                        Case 2
                            strDay = "Lunedì"
                        Case 3
                            strDay = "Martedì"
                        Case 4
                            strDay = "Mercoledì"
                        Case 5
                            strDay = "Giovedì"
                        Case 6
                            strDay = "Venerdì"
                        Case 7
                            strDay = "Sabato"
                    End Select
                End If
                Dim parameters As RouteValueDictionary
                parameters = New RouteValueDictionary(New With {.TipoLocale = Trim(dr("Genere")), .NomeLocale = Trim(dr("Organizzatore")), .TitoloEvento = Trim(dr("NomeEvento"))})

                Dim vpd As VirtualPathData
                vpd = RouteTable.Routes.GetVirtualPath(Nothing, "PaginaEvento", parameters)

                dr("NomeEvento") = dr("Comune") & " - " & strDay & " " & dr("DataInizio") & " - " & dr("Organizzatore") & " - " & dr("NomeEvento")
                'dr("LinkInterno") = dr("LinkInterno") & "&NomeEvento=" & dr("NomeEvento")

                dr("LinkInterno") = vpd.VirtualPath & "?PK_Evento=" & dr("PK_Evento")
            Next
            ' Throw New Exception(ds.Tables.Count)
            RptEventi2.DataSource = ds
            RptEventi2.DataBind()
        End If





        sql = "SELECT max(T.PK_Target),  I.Comune as Comune, E.NomeEvento as NomeEvento,  E.PK_Evento," & _
            "E.DataInizio, E.DataFine, E.Priorita, E.Descrizione as DescrizioneEvento, " & _
          " CASE isnull(E.imgEvento, '') WHEN '' then MAX(T.TargetImgURLSmall) else E.imgEvento end as ImgEvento, " & _
          " convert(varchar,E.DataInizio,103) as DataInizio, " & _
          " convert(varchar,E.DataFine,103) as DataFine, E.Link, E.Brochure, " & _
         " CASE isnull(E.intCategoria, 100) " & _
                      "     WHEN 100 then 'Generico' " & _
                      "     WHEN 1 then 'Concerto'" & _
                      "     WHEN 2 then 'Festa'" & _
                      "     WHEN 3 then 'Discoteca'" & _
                      "     WHEN 4 then 'Fiera'" & _
                      "     WHEN 5 then 'Sagra'" & _
                      "     WHEN 6 then 'Gita'" & _
                      "     WHEN 7 then 'Gratis'" & _
                      "     WHEN 8 then 'Mostra' end as Genere, " & _
          " CASE isnull(E.link, '') WHEN '' then 0 else 1 end as LinkPresente, " & _
          " CASE isnull(E.Brochure, '') WHEN '' then 0 else 1 end as BrochurePresente, " & _
               " isnull(I.Strada,'') + ' ' + isnull(I.Civico,'') + ' ' + isnull(I.Comune,'') + ' ' + isnull(I.Presso,'') as Indirizzo, " & _
                 " MAX(T.TargetImgURLSmall) as TargetImgURLSmall, " & _
                 " A.Denominazione as Organizzatore, " & _
                 " ROW_NUMBER() OVER(ORDER BY E.DataInizio) AS 'RowNumber'" & _
                 " FROM Evento E " & _
                 " LEFT OUTER JOIN Indirizzo I ON I.PK_Indirizzo = FK_Indirizzo " & _
                 " LEFT OUTER JOIN Evento_Organizzatore EO ON PK_Evento = EO.FK_Evento " & _
                 " LEFT OUTER JOIN Organizzatore O ON O.PK_Organizzatore = EO.FK_Organizzatore " & _
                 " LEFT OUTER JOIN Anagrafica A ON A.PK_Anagrafica = O.FK_Anagrafica " & _
                 " LEFT OUTER JOIN Evento_Target ET ON ET.FK_Evento = PK_Evento " & _
                 " LEFT OUTER JOIN Target T ON T.PK_Target = ET.FK_Target " & _
                 " WHERE E.Deleted= 0 AND (E.DataInizio = CAST('" & CurrentDate.AddDays(1).ToString("yyyyMMdd") & "' AS DATE)" & _
                    " OR (E.DataInizio < CAST('" & CurrentDate.AddDays(1).ToString("yyyyMMdd") & "' as date) " & _
                    " AND E.DataFine >= CAST('" & CurrentDate.AddDays(1).ToString("yyyyMMdd") & "' as date)))" & _
                 " GROUP BY E.NomeEvento, E.Descrizione, E.DataInizio, E.DataFine, E.Link, I.Strada, I.Civico, I.Comune, I.Presso, PK_Evento, E.imgEvento, E.Brochure, E.Priorita, " & _
                 " E.intCategoria, A.Denominazione" & _
                  " ORDER BY E.Priorita DESC, Comune "

        'Throw New Exception(sql)
        ds = dbConnector.FillDS(sql, Nothing, 1)

        '<%#DataBinder.Eval(Container.DataItem, "ImageTargetId")%>'
        If Not IsNothing(ds) AndAlso ds.Tables.Count > 0 Then
            ds.Tables(0).Columns.Add("ItemTemplateId")
            ds.Tables(0).Columns.Add("ImageApriId")
            ds.Tables(0).Columns.Add("LinkInterno")
            For Each dr As DataRow In ds.Tables(0).Rows
                dr("ItemTemplateId") = "ItemTemplate_" & 1 & "_" & dr("RowNumber")
                dr("ImageApriId") = "ImageApri_" & 1 & "_" & dr("RowNumber")
                Dim strDay = ""
                If Not IsDBNull(dr("DataInizio")) AndAlso Not IsDBNull(dr("DataFine")) AndAlso dr("DataInizio") = dr("DataInizio") Then
                    Dim wd As Integer = Weekday(CDate(dr("DataInizio")))

                    Select Case wd
                        Case 1
                            strDay = "Domenica"
                        Case 2
                            strDay = "Lunedì"
                        Case 3
                            strDay = "Martedì"
                        Case 4
                            strDay = "Mercoledì"
                        Case 5
                            strDay = "Giovedì"
                        Case 6
                            strDay = "Venerdì"
                        Case 7
                            strDay = "Sabato"
                    End Select
                End If
                Dim parameters As RouteValueDictionary
                parameters = New RouteValueDictionary(New With {.TipoLocale = Trim(dr("Genere")), .NomeLocale = Trim(dr("Organizzatore")), .TitoloEvento = Trim(dr("NomeEvento"))})

                Dim vpd As VirtualPathData
                vpd = RouteTable.Routes.GetVirtualPath(Nothing, "PaginaEvento", parameters)

                dr("NomeEvento") = dr("Comune") & " - " & strDay & " " & dr("DataInizio") & " - " & dr("Organizzatore") & " - " & dr("NomeEvento")
                'dr("LinkInterno") = dr("LinkInterno") & "&NomeEvento=" & dr("NomeEvento")

                dr("LinkInterno") = vpd.VirtualPath & "?PK_Evento=" & dr("PK_Evento")
            Next
            ' Throw New Exception(ds.Tables.Count)
            RptEventi3.DataSource = ds
            RptEventi3.DataBind()
        End If


        sql = "SELECT max(T.PK_Target), I.Comune as Comune, E.NomeEvento as NomeEvento,  E.PK_Evento," & _
            "E.DataInizio, E.DataFine, E.Priorita, E.Descrizione as DescrizioneEvento, " & _
          " CASE isnull(E.imgEvento, '') WHEN '' then MAX(T.TargetImgURLSmall) else E.imgEvento end as ImgEvento, " & _
          " convert(varchar,E.DataInizio,103) as DataInizio, " & _
          " convert(varchar,E.DataFine,103) as DataFine, E.Link, E.Brochure, " & _
           " CASE isnull(E.intCategoria, 100) " & _
                      "     WHEN 100 then 'Generico' " & _
                      "     WHEN 1 then 'Concerto'" & _
                      "     WHEN 2 then 'Festa'" & _
                      "     WHEN 3 then 'Discoteca'" & _
                      "     WHEN 4 then 'Fiera'" & _
                      "     WHEN 5 then 'Sagra'" & _
                      "     WHEN 6 then 'Gita'" & _
                      "     WHEN 7 then 'Gratis'" & _
                      "     WHEN 8 then 'Mostra' end as Genere, " & _
          " CASE isnull(E.link, '') WHEN '' then 0 else 1 end as LinkPresente, " & _
          " CASE isnull(E.Brochure, '') WHEN '' then 0 else 1 end as BrochurePresente, " & _
               " isnull(I.Strada,'') + ' ' + isnull(I.Civico,'') + ' ' + isnull(I.Comune,'') + ' ' + isnull(I.Presso,'') as Indirizzo, " & _
                 " MAX(T.TargetImgURLSmall) as TargetImgURLSmall, " & _
                 " A.Denominazione as Organizzatore, " & _
                 " ROW_NUMBER() OVER(ORDER BY E.DataInizio) AS 'RowNumber'" & _
                 " FROM Evento E " & _
                 " LEFT OUTER JOIN Indirizzo I ON I.PK_Indirizzo = FK_Indirizzo " & _
                 " LEFT OUTER JOIN Evento_Organizzatore EO ON PK_Evento = EO.FK_Evento " & _
                 " LEFT OUTER JOIN Organizzatore O ON O.PK_Organizzatore = EO.FK_Organizzatore " & _
                 " LEFT OUTER JOIN Anagrafica A ON A.PK_Anagrafica = O.FK_Anagrafica " & _
                 " LEFT OUTER JOIN Evento_Target ET ON ET.FK_Evento = PK_Evento " & _
                 " LEFT OUTER JOIN Target T ON T.PK_Target = ET.FK_Target " & _
                " WHERE E.Deleted= 0 AND E.DataFine >= CAST('" & CurrentDate.ToString("yyyyMMdd") & "' as date)" & _
                 " GROUP BY E.NomeEvento, E.Descrizione, E.DataInizio, E.DataFine, E.Link, I.Strada, I.Civico, I.Comune, I.Presso, PK_Evento, E.imgEvento, E.Brochure, E.Priorita, " & _
                 " E.intCategoria, A.Denominazione" & _
                  " ORDER BY E.Priorita DESC, Comune "



        ' Throw New Exception(sql)
        ds = dbConnector.FillDS(sql, Nothing, 1)

        '<%#DataBinder.Eval(Container.DataItem, "ImageTargetId")%>'
        If Not IsNothing(ds) AndAlso ds.Tables.Count > 0 Then
            ds.Tables(0).Columns.Add("ItemTemplateId")
            ds.Tables(0).Columns.Add("ImageApriId")
            ds.Tables(0).Columns.Add("LinkInterno")
            For Each dr As DataRow In ds.Tables(0).Rows
                dr("ItemTemplateId") = "ItemTemplate_" & 5 & "_" & dr("RowNumber")
                '  dr("LinkInterno") = dr("LinkInterno") & "&NomeEvento=" & dr("NomeEvento")
                dr("ImageApriId") = "ImageApri_" & 1 & "_" & dr("RowNumber")
                Dim strDay = ""
                If Not IsDBNull(dr("DataInizio")) AndAlso Not IsDBNull(dr("DataFine")) AndAlso dr("DataInizio") = dr("DataInizio") Then
                    Dim wd As Integer = Weekday(CDate(dr("DataInizio")))

                    Select Case wd
                        Case 1
                            strDay = "Domenica"
                        Case 2
                            strDay = "Lunedì"
                        Case 3
                            strDay = "Martedì"
                        Case 4
                            strDay = "Mercoledì"
                        Case 5
                            strDay = "Giovedì"
                        Case 6
                            strDay = "Venerdì"
                        Case 7
                            strDay = "Sabato"
                    End Select
                End If
                Dim parameters As RouteValueDictionary
                parameters = New RouteValueDictionary(New With {.TipoLocale = Trim(dr("Genere")), .NomeLocale = Trim(dr("Organizzatore")), .TitoloEvento = Trim(dr("NomeEvento"))})

                Dim vpd As VirtualPathData
                vpd = RouteTable.Routes.GetVirtualPath(Nothing, "PaginaEvento", parameters)

                dr("NomeEvento") = dr("Comune") & " - " & strDay & " " & dr("DataInizio") & " - " & dr("Organizzatore") & " - " & dr("NomeEvento")
                'dr("LinkInterno") = dr("LinkInterno") & "&NomeEvento=" & dr("NomeEvento")

                dr("LinkInterno") = vpd.VirtualPath & "?PK_Evento=" & dr("PK_Evento")
            Next
            ' Throw New Exception(ds.Tables.Count)
            RptEventiAllHide.DataSource = ds
            RptEventiAllHide.DataBind()
        End If

        ' Next

        ' Catch ex As Exception
        '  Throw New Exception(sql)
        '  End Try
    End Sub


    Protected Sub BtnSearch_Click(sender As Object, e As System.EventArgs) Handles BtnSearch.Click

        Dim sql As String = ""
        ' Try

        Dim ds As New DataSet
        ' Dim FK_target As New Integer
        Dim dbConnector As New DBObject
        Dim CurrentDate As DateTime = DateTime.Now.Date()


        'Dim rptEventis As New List(Of Repeater)
        'rptEventis.Add(RptEventi1)
        'rptEventis.Add(RptEventi2)

        '  For i As Integer = 0 To 1
        sql = "SELECT max(T.PK_Target) as FK_Target,I.Comune as Comune, E.NomeEvento as NomeEvento, E.PK_evento, " & _
            " E.DataInizio, E.DataFine, E.Priorita, E.Descrizione as DescrizioneEvento, " & _
          " CASE isnull(E.imgEvento, '') WHEN '' then MAX(T.TargetImgURLSmall) else E.imgEvento end as ImgEvento, " & _
          " convert(varchar,E.DataInizio,103) as DataInizio, " & _
          " convert(varchar,E.DataFine,103) as DataFine, E.Link, E.Brochure, " & _
            " CASE isnull(E.intCategoria, 100) " & _
                      "     WHEN 100 then 'Generico' " & _
                      "     WHEN 1 then 'Concerto'" & _
                      "     WHEN 2 then 'Festa'" & _
                      "     WHEN 3 then 'Discoteca'" & _
                      "     WHEN 4 then 'Fiera'" & _
                      "     WHEN 5 then 'Sagra'" & _
                      "     WHEN 6 then 'Gita'" & _
                      "     WHEN 7 then 'Gratis'" & _
                      "     WHEN 8 then 'Mostra' end as Genere, " & _
          " CASE isnull(E.link, '') WHEN '' then 0 else 1 end as LinkPresente, " & _
          " CASE isnull(E.Brochure, '') WHEN '' then 0 else 1 end as BrochurePresente, " & _
               " isnull(I.Strada,'') + ' ' + isnull(I.Civico,'') + ' ' + isnull(I.Comune,'') + ' ' + isnull(I.Presso,'') as Indirizzo, " & _
                 " MAX(T.TargetImgURLSmall) as TargetImgURLSmall, " & _
                 " A.Denominazione as Organizzatore, " & _
                 " ROW_NUMBER() OVER(ORDER BY E.DataInizio) AS 'RowNumber'" & _
                 " FROM Evento E " & _
                 " LEFT OUTER JOIN Indirizzo I ON I.PK_Indirizzo = FK_Indirizzo " & _
                 " LEFT OUTER JOIN Evento_Organizzatore EO ON PK_Evento = EO.FK_Evento " & _
                 " LEFT OUTER JOIN Organizzatore O ON O.PK_Organizzatore = EO.FK_Organizzatore " & _
                 " LEFT OUTER JOIN Anagrafica A ON A.PK_Anagrafica = O.FK_Anagrafica " & _
                 " LEFT OUTER JOIN Evento_Target ET ON ET.FK_Evento = PK_Evento " & _
                 " LEFT OUTER JOIN Target T ON T.PK_Target = ET.FK_Target " & _
                 " WHERE E.Deleted = 0 AND E.DataInizio >= CAST('" & CurrentDate.ToString("yyyyMMdd") & "' AS DATE) " & _
                 " AND (E.NomeEvento like '%" & TbTextToSearch.Text.Replace(" ", "%") & "%' " & _
                 " OR E.Descrizione like '%" & TbTextToSearch.Text.Replace(" ", "%") & "%' " & _
                 " OR E.link like '%" & TbTextToSearch.Text.Replace(" ", "%") & "%' " & _
                 " OR isnull(I.Strada,'') + ' ' + isnull(I.Civico,'') + ' ' + isnull(I.Comune,'') + ' ' + isnull(I.Presso,'') like '%" & TbTextToSearch.Text.Replace(" ", "%") & "%' " & _
                 " OR A.Denominazione like '%" & TbTextToSearch.Text.Replace(" ", "%") & "%') " & _
                 " GROUP BY E.NomeEvento, E.Descrizione, E.DataInizio, E.DataFine, E.Link, I.Strada, I.Civico, I.Comune, I.Presso, PK_Evento, E.imgEvento, E.Brochure, E.Priorita," & _
                 " E.intCategoria, A.Denominazione" & _
                  " ORDER BY E.Priorita DESC, Comune "
        '  Throw New Exception(sql)


        ds = dbConnector.FillDS(sql, Nothing, 1)

        If Not IsNothing(ds) AndAlso ds.Tables.Count > 0 Then
            ds.Tables(0).Columns.Add("ItemTemplateId")
            ds.Tables(0).Columns.Add("ImageApriId")
            ds.Tables(0).Columns.Add("LinkInterno")
            For Each dr As DataRow In ds.Tables(0).Rows

                dr("ItemTemplateId") = "ItemTemplate_" & dr("RowNumber")
                dr("ImageApriId") = "ImageApri_" & dr("RowNumber")
                Dim strDay = ""
                If Not IsDBNull(dr("DataInizio")) AndAlso Not IsDBNull(dr("DataFine")) AndAlso dr("DataInizio") = dr("DataInizio") Then
                    Dim wd As Integer = Weekday(CDate(dr("DataInizio")))

                    Select Case wd
                        Case 1
                            strDay = "Domenica"
                        Case 2
                            strDay = "Lunedì"
                        Case 3
                            strDay = "Martedì"
                        Case 4
                            strDay = "Mercoledì"
                        Case 5
                            strDay = "Giovedì"
                        Case 6
                            strDay = "Venerdì"
                        Case 7
                            strDay = "Sabato"
                    End Select
                End If
                Dim parameters As RouteValueDictionary
                parameters = New RouteValueDictionary(New With {.TipoLocale = Trim(dr("Genere")), .NomeLocale = Trim(dr("Organizzatore")), .TitoloEvento = Trim(dr("NomeEvento"))})

                Dim vpd As VirtualPathData
                vpd = RouteTable.Routes.GetVirtualPath(Nothing, "PaginaEvento", parameters)

                dr("NomeEvento") = dr("Comune") & " - " & strDay & " " & dr("DataInizio") & " - " & dr("Organizzatore") & " - " & dr("NomeEvento")
                'dr("LinkInterno") = dr("LinkInterno") & "&NomeEvento=" & dr("NomeEvento")

                dr("LinkInterno") = vpd.VirtualPath & "?PK_Evento=" & dr("PK_Evento")
            Next
            ' Throw New Exception(ds.Tables.Count)
            RptEventi4.DataSource = ds
            RptEventi4.DataBind()
        End If
        'Page.ClientScript.RegisterClientScriptBlock(Me.GetType(), "SelectSearchPanel", "ShowSearchPanel()", True)
    End Sub

End Class
